package com.djf.djfclickhouse.dao.impl;

import com.djf.djfclickhouse.dao.OrderDao;
import com.djf.djfclickhouse.entity.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 订单数据访问实现类
 */
@Repository
public class OrderDaoImpl implements OrderDao {
    
    @Autowired
    private DataSource clickHouseDataSource;
    
    @Override
    public int insert(Order order) {
        String sql = "INSERT INTO orders (id, order_no, tenant_id, user_id, product_name, product_price, " +
                "quantity, total_amount, order_status, payment_method, shipping_address, remark, " +
                "create_time, update_time, deleted) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setLong(1, order.getId());
            ps.setString(2, order.getOrderNo());
            ps.setLong(3, order.getTenantId());
            ps.setLong(4, order.getUserId());
            ps.setString(5, order.getProductName());
            ps.setBigDecimal(6, order.getProductPrice());
            ps.setInt(7, order.getQuantity());
            ps.setBigDecimal(8, order.getTotalAmount());
            ps.setInt(9, order.getOrderStatus());
            ps.setString(10, order.getPaymentMethod());
            ps.setString(11, order.getShippingAddress());
            ps.setString(12, order.getRemark());
            ps.setTimestamp(13, Timestamp.valueOf(order.getCreateTime()));
            ps.setTimestamp(14, Timestamp.valueOf(order.getUpdateTime()));
            ps.setInt(15, order.getDeleted());
            
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("插入订单失败", e);
        }
    }
    
    @Override
    public int batchInsert(List<Order> orders) {
        String sql = "INSERT INTO orders (id, order_no, tenant_id, user_id, product_name, product_price, " +
                "quantity, total_amount, order_status, payment_method, shipping_address, remark, " +
                "create_time, update_time, deleted) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            for (Order order : orders) {
                ps.setLong(1, order.getId());
                ps.setString(2, order.getOrderNo());
                ps.setLong(3, order.getTenantId());
                ps.setLong(4, order.getUserId());
                ps.setString(5, order.getProductName());
                ps.setBigDecimal(6, order.getProductPrice());
                ps.setInt(7, order.getQuantity());
                ps.setBigDecimal(8, order.getTotalAmount());
                ps.setInt(9, order.getOrderStatus());
                ps.setString(10, order.getPaymentMethod());
                ps.setString(11, order.getShippingAddress());
                ps.setString(12, order.getRemark());
                ps.setTimestamp(13, Timestamp.valueOf(order.getCreateTime()));
                ps.setTimestamp(14, Timestamp.valueOf(order.getUpdateTime()));
                ps.setInt(15, order.getDeleted());
                ps.addBatch();
            }
            
            int[] results = ps.executeBatch();
            return results.length;
        } catch (SQLException e) {
            throw new RuntimeException("批量插入订单失败", e);
        }
    }
    
    @Override
    public Order selectById(Long id) {
        String sql = "SELECT * FROM orders WHERE id = ? AND deleted = 0";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToOrder(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new RuntimeException("查询订单失败", e);
        }
    }
    
    @Override
    public Order selectByOrderNo(String orderNo) {
        String sql = "SELECT * FROM orders WHERE order_no = ? AND deleted = 0";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setString(1, orderNo);
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToOrder(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new RuntimeException("查询订单失败", e);
        }
    }
    
    @Override
    public List<Order> selectByUserId(Long userId) {
        String sql = "SELECT * FROM orders WHERE user_id = ? AND deleted = 0 ORDER BY create_time DESC";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setLong(1, userId);
            ResultSet rs = ps.executeQuery();
            
            List<Order> orders = new ArrayList<>();
            while (rs.next()) {
                orders.add(mapResultSetToOrder(rs));
            }
            return orders;
        } catch (SQLException e) {
            throw new RuntimeException("查询用户订单失败", e);
        }
    }
    
    @Override
    public List<Order> selectWithPage(int offset, int limit) {
        String sql = "SELECT * FROM orders WHERE deleted = 0 ORDER BY create_time DESC LIMIT ? OFFSET ?";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setInt(1, limit);
            ps.setInt(2, offset);
            ResultSet rs = ps.executeQuery();
            
            List<Order> orders = new ArrayList<>();
            while (rs.next()) {
                orders.add(mapResultSetToOrder(rs));
            }
            return orders;
        } catch (SQLException e) {
            throw new RuntimeException("分页查询订单失败", e);
        }
    }
    
    @Override
    public long countByCondition(Long userId, Integer orderStatus) {
        StringBuilder sql = new StringBuilder("SELECT count(*) FROM orders WHERE deleted = 0");
        
        if (userId != null) {
            sql.append(" AND user_id = ?");
        }
        if (orderStatus != null) {
            sql.append(" AND order_status = ?");
        }
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            
            int paramIndex = 1;
            if (userId != null) {
                ps.setLong(paramIndex++, userId);
            }
            if (orderStatus != null) {
                ps.setInt(paramIndex, orderStatus);
            }
            
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
            return 0;
        } catch (SQLException e) {
            throw new RuntimeException("统计订单数量失败", e);
        }
    }
    
    @Override
    public int updateOrderStatus(Long id, Integer orderStatus) {
        String sql = "ALTER TABLE orders UPDATE order_status = ?, update_time = ? WHERE id = ?";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setInt(1, orderStatus);
            ps.setTimestamp(2, Timestamp.valueOf(LocalDateTime.now()));
            ps.setLong(3, id);
            
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("更新订单状态失败", e);
        }
    }
    
    @Override
    public int deleteById(Long id) {
        String sql = "ALTER TABLE orders UPDATE deleted = 1, update_time = ? WHERE id = ?";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setTimestamp(1, Timestamp.valueOf(LocalDateTime.now()));
            ps.setLong(2, id);
            
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("逻辑删除订单失败", e);
        }
    }
    
    @Override
    public int physicalDeleteById(Long id) {
        String sql = "ALTER TABLE orders DELETE WHERE id = ?";
        
        try (Connection conn = clickHouseDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setLong(1, id);
            
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("物理删除订单失败", e);
        }
    }
    
    /**
     * 将ResultSet映射为Order对象
     */
    private Order mapResultSetToOrder(ResultSet rs) throws SQLException {
        Order order = new Order();
        order.setId(rs.getLong("id"));
        order.setOrderNo(rs.getString("order_no"));
        order.setTenantId(rs.getLong("tenant_id"));
        order.setUserId(rs.getLong("user_id"));
        order.setProductName(rs.getString("product_name"));
        order.setProductPrice(rs.getBigDecimal("product_price"));
        order.setQuantity(rs.getInt("quantity"));
        order.setTotalAmount(rs.getBigDecimal("total_amount"));
        order.setOrderStatus(rs.getInt("order_status"));
        order.setPaymentMethod(rs.getString("payment_method"));
        order.setShippingAddress(rs.getString("shipping_address"));
        order.setRemark(rs.getString("remark"));
        
        Timestamp createTime = rs.getTimestamp("create_time");
        if (createTime != null) {
            order.setCreateTime(createTime.toLocalDateTime());
        }
        
        Timestamp updateTime = rs.getTimestamp("update_time");
        if (updateTime != null) {
            order.setUpdateTime(updateTime.toLocalDateTime());
        }
        
        order.setDeleted(rs.getInt("deleted"));
        return order;
    }
}